package Chat;



import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


import Entity.*;

import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Iterator;




public class mysqlConnection {
	
	
	private Statement stmt;
	private Statement stmt_mail; 
	private ResultSet rs = null;
	private ResultSet rs1 = null;	
	private Object result;
	private Connection conn;
	
	
	public mysqlConnection(Statement s){
		this.stmt_mail=s;
	}
	public mysqlConnection()
	{
		try 
		{
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (Exception ex) {/* handle the error*/}
        
        try 
        {



            conn = DriverManager.getConnection("jdbc:mysql://localhost/icmproject","root","XE7111");




            System.out.println("SQL connection succeed");
     	} catch (SQLException ex) 
     	    {/* handle any errors*/
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
            }
        try{
        	stmt = conn.createStatement();
        	stmt_mail=conn.createStatement();
        }catch(Exception ex){}
        }
	public Object freez_req(int req_number){
		
		try {
			stmt.executeUpdate("insert into requeststatus(r_id,reqStatus,reqDate) values('"+req_number+"','FROZEN',CURRENT_TIMESTAMP())");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}

		return true;
	}
	/**
	 * this method update status of extended request
	 * @param extension conrains the request number and stage number
	 */
	
	public void update_extended_req_status(Extention_Stage_req extension){
		try {
			stmt.executeUpdate("UPDATE extantionreq  SET status='EXTENDED' WHERE r_id='"+extension.getR_id().intValue()+"' and stageID='"+extension.getStage_id().intValue()+"' and status='WAITING'");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * this method update status of extention request to rejected
	 * @param extension conrains the request number and stage number
	 */
	
	public Boolean update_exte_req_rejected(Extention_Stage_req extension){
		try {
			stmt.executeUpdate("UPDATE extantionreq  SET status='REJECTED' WHERE r_id='"+extension.getR_id().intValue()+"' and stageID='"+extension.getStage_id().intValue()+"' and status='WAITING'");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		    return false;
		}
		return true;
	}
	/**
	 * this method will  find the record that match the stage_id and request number
	 *  and update the duration
	 * @param entity that contains the request number and stage id that needs to be extend and the number of days to extend
	 * @return true if the method succeed on updating and false otherwise
	 */
	
	public Boolean extend_stage(Extention_Stage_req extension){
		result=new Boolean("false");
	
		try {
			
			//create view that contains for the relevat pair of (request number,stage_id) the max open date- that is the way to know
			//that this stage is not closed
			stmt.executeUpdate("create view req_max_dates as select r_id,stageID,max(openDate) as max_date from stageinreq where r_id='"+extension.getR_id().intValue()+"' and stageID='"+extension.getStage_id().intValue()+"' group by r_id,stageID");
			  stmt.executeUpdate(" UPDATE stageinreq  SET duration=duration+'"+extension.getNumDay().intValue()+"' where ( r_id =(select r_id from req_max_dates) and stageID =(select stageID from req_max_dates) and openDate =(select max_date from req_max_dates)) ");
			result=Boolean.TRUE;
			update_extended_req_status(extension);
			
		}	catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();	
		}
	try {
		stmt.executeUpdate("drop view req_max_dates");
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return (Boolean) result;
	}

	/**
	 * this method will find the current stage of a specific request
	 * @param req_num is the request number of the request
	 * @return class that contain the parameters:current stage name and open stage date
	 */
		public Curr_stage Get_cur_stage(int req_num){
			Curr_stage cur=new Curr_stage();
			try {
				
				stmt=conn.createStatement();
				
				stmt.executeUpdate("CREATE VIEW reqStage AS select openDate from stageinreq where r_id='"+req_num+"'");
				                      
				rs=stmt.executeQuery("select stageID,DATE(openDate) as date,duration from stageinreq where r_id='"+req_num+"' and openDate=(select MAX(openDate) from( reqStage ))");
				
				if (rs.next()){
				cur.setCurrent_stage(rs.getInt("stageID"));
				cur.setOpen(rs.getDate("date"));
		        cur.setDuration(rs.getInt("duration"));
			}} 
			catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				stmt.executeUpdate("drop view reqStage");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return cur;}
		

	/**
	 * this method will "unfreeze" frozen request and update the correct duration for 
	 * the last stage the request was in
	 * @param req_id is the number of the request 
	 * @return stage that will contain the cuurent stage,the updated duration for the stage
	 */
	

	public Object unfreezReq(int req_id){
		
		try{
			stmt=conn.createStatement();
		
			
			Curr_stage curr= Get_cur_stage(req_id);
			
			stmt.executeUpdate("CREATE VIEW dates1 AS select reqDate from requeststatus where reqStatus='FROZEN' and r_id='"+req_id+"'");
			rs=stmt.executeQuery("select DATE(reqDate) as freeze_date from requeststatus where r_id='"+req_id+"'  and reqDate=(select MAX(reqDate) from( dates1 ))");
			
			if (rs.next()){
				/*
				 * calculate (duration-(freeze date-open_stage date)+(today-freeze date)) in order to update the correct duration
				 */
				
				Date freezeDate=rs.getDate("freeze_date");//date of freezing request
				Date openDtae=curr.getOpen();//date of open current stage
			    long day_in_millis = 1000 * 60 * 60 * 24;
				int days_between_open_to_freeze = (int) ((freezeDate.getTime() - openDtae.getTime())/ day_in_millis );
				Date today = new Date(System.currentTimeMillis());
				int days_between_today_to_freeze = (int) ((today.getTime() - freezeDate.getTime())/ day_in_millis );
				System.out.println(days_between_open_to_freeze);
				System.out.println(days_between_today_to_freeze);
				System.out.println(curr.getDuration());
				int new_dur=(curr.getDuration()-days_between_open_to_freeze+days_between_today_to_freeze);
			     System.out.println(new_dur);
			    //update request to "ACTIVE"
			    stmt.executeUpdate("INSERT INTO requeststatus (`r_id`, `reqStatus`,`reqDate`) " + " " +
						" VALUES ('"+req_id+"', 'ACTIVE',current_timestamp())");
			  
			    stmt.executeUpdate(" create view  d as select MAX(openDate) as open_date from stageinreq where r_id='"+req_id+"' and stageID='"+curr.getCurrent_stage()+"' group by r_id,stageID");

				stmt.executeUpdate("UPDATE stageinreq  SET duration='"+new_dur+"' WHERE r_id='"+req_id+"'and stageID='"+curr.getCurrent_stage()+"'and openDate=(select * from d) ");



				
			}}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return false;

			}
		try {
			stmt.executeUpdate("drop view dates1");
			  stmt.executeUpdate("drop view d");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
			return true ;}
			
		

	
	/**
	 * This method checks weather the user exist in the data base or not
	 * @param user contains user and password of the user that is trying to login
	 * @return  
	 */
	public Object loggin_check(User user){
	

		try {

             rs = stmt.executeQuery("SELECT user_name,password,is_logged FROM user WHERE user_name='"+user.getUserName()+"'AND password ='"+user.getPassword()+"'");		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();}
			try {
				if (rs.next()){
					if (rs.getBoolean("is_logged"))
						return 1;
						
					else {
						stmt.executeUpdate("update user set is_logged =1 where user_name="+user.getUserName());
						return 2;
					}
						
					
						
					}}
			 
			
			catch (SQLException e) {
				e.printStackTrace();
			}
			
           return 0; 
		}
	
	public Object logOut(Integer userId){
		try{
			stmt.executeUpdate("update user set is_logged =0 where user_name="+userId);
			rs.close();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return true;
	}
	/**
	 
	 * This method get all the list of authorization per request for a specific user
	 * @param id is the value of the user id that logged to the system
	 * @return reqId_auto is a container with contain requestId as the key and a list of 
	 		  user's authorization on this request as the value 
	 */
	public Object getPerrmisions(User user){
		
		String s = new String();
		ArrayList<Integer> autoList = new ArrayList<Integer>();
		String[] reqList = new String [30];
		int i=0;
		HashMap<Integer, ArrayList<Integer>> reqId_auto = new HashMap<Integer, ArrayList<Integer>>();
		//reqId_auto=null;
		try{
		s =  "SELECT r_Id, GROUP_CONCAT(autoNum) as autoNum FROM userinreq WHERE username = ? GROUP BY r_id ";
		PreparedStatement preparedStatement = conn.prepareStatement(s);
		preparedStatement.setInt(1, user.getUserName());
		 rs = preparedStatement.executeQuery();
		while(rs.next()){
			String [] tmp = rs.getString("autoNum").split(",");
				for (String x:tmp) {
					autoList.add(Integer.parseInt(x));
			}
		reqId_auto.put(rs.getInt("r_Id"), autoList);
		reqList[i] = rs.getString("r_Id");
		}
		user.setReq_auto_list(reqId_auto);
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//user.setReq_auto_list(reqId_auto);
		return user;
	}
	
	
	/**
	 * this method find the role of the user
	 * @param user contain details of the user
	 * @return user with his role
	 */
	
	public Object get_role(User user){
		try {
			rs=stmt.executeQuery("select role from user where user_name='"+user.getUserName()+"' ");
		    if(rs.next()){
		    	result =  rs.getInt("role");
		    }
		    else 
		    	result=null;
		
		
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return result;
		
	}
/**
 * Tish method return all the reuquests athached to the given user
 * @param user ditails
 * @return user with the acording requests
 */
	public Object get_prmissions(User user){
		Integer i=(Integer) get_role(user);
		user.setRole(i);
		if(i<5){
			try {
				rs=stmt.executeQuery("select r_id from request");
			    while (rs.next()){
			    	user.getReq_list().add(rs.getString("r_id"));
			    }
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}else 
			user=(User) getReqQuery(user);
		return user;
		}

	 /**
	 * This method get all the list of authorization per request for a specific user
	 * @param id is the value of the user id that logged to the system
	 * @return reqId_auto is a container with contain requestId as the key and a list of 
	 		  user's authorization on this request as the value 
	 */
	public Object getReqQuery(User user){
		String s = new String();
		HashMap<Integer, ArrayList<Integer>> reqId_auto = new HashMap<Integer, ArrayList<Integer>>();
		try{
		s =  "SELECT autoNum, GROUP_CONCAT(r_Id) as r_Id FROM userinreq WHERE username = ? GROUP BY autoNum ";
		PreparedStatement preparedStatement = conn.prepareStatement(s);
		preparedStatement.setInt(1, user.getUserName());
		ResultSet rs = preparedStatement.executeQuery();
		while(rs.next()){
			ArrayList<Integer> reqList = new ArrayList<Integer>();
			String [] tmp = rs.getString("r_Id").split(",");
				for (String x:tmp) {
					reqList.add(Integer.parseInt(x));
			}
		reqId_auto.put(rs.getInt("autoNum"), reqList);
		}
		user.setReq_auto_list(reqId_auto);
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return user;
	}
	/**
	 * this method will find the current stage of a specific request
	 * @param req_num is the request number of the request
	 * @return Array that contain the parameters:current stage name and expected_close stage date
	 */
		public Object viewReq(int req_num){
			String [] s = new String[3];
			s[0]=(String) getRequestStatus(req_num);
			if(s[0].equals("FROZEN")||s[0].equals("CLOSED")){
				s[1]="N/A";
				s[2]="N/A";
				
			
				return s;}
			try {
				stmt=conn.createStatement();
				
				stmt.executeUpdate("CREATE VIEW reqStage AS select openDate from stageinreq where r_id='"+req_num+"'");
				                      
				rs=stmt.executeQuery("select stageID,DATE(openDate+ INTERVAL  duration DAY) as expected_close from stageinreq where r_id='"+req_num+"' and openDate=(select MAX(openDate) from( reqStage ))");
				
				if (rs.next()){
					
					s[1]=rs.getString("stageID");
					s[2]=rs.getString("expected_close");}
			result=s;
			stmt.executeUpdate("drop view reqStage");	
				
			} 
			catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return result;}
		
		/**
		 * this method will calculate the number and sum of all late requests
		 * @param t_rep contain the stars and end day to generate the report  
		 * @return tardiness_repord that contain thr information to generate the tardiness report
		 */
		

	

		public Object tardiness_report (TardinessReport_ENTITY t_rep){



		
		String from=t_rep.getFrom();
		String to=t_rep.getTo();
		
		try {
			

	// creating view that will contain expected closure date and the closure date
			
			stmt.executeUpdate("create view dates as SELECT DATE(openDate+ INTERVAL  duration DAY )as new_date,closureDate,r_id,stageID from stageinreq");
	// create view with all the late requests		
			
			stmt.executeUpdate("create view late_req as select r_id,stageID,new_date,closureDate from dates where((new_date < current_date() and closureDate is null)or (new_date < closureDate and closureDate is not null) )and  (new_date >=  \""+from+"\"and new_date <=  \""+to+"\")");
	// create view of request_id, the latest date we updated new status		
		
			stmt.executeUpdate("create view req_max_dates as select r_id,max(reqDate) as max_date from requeststatus group by r_id");
	// create view with all the current frozen requests		
		
			stmt.executeUpdate("create view frozen_req as select requeststatus.r_id from requeststatus,req_max_dates where requeststatus.r_id=req_max_dates.r_id and requeststatus.reqStatus='FROZEN' and requeststatus.reqDate=req_max_dates.max_date");
	// create view with all the delay requests and the number of the dayes in late
		//	
			
			
	// union between requests that is closed and open		
			stmt.executeUpdate("create view late_req_true as select late_req.r_id as r_id,late_req.stageID as stage_id,late_req.closureDate-late_req.new_date as late_dur,request.in_system as in_sys from late_req,request where late_req.r_id=request.r_id and late_req.r_id not in(select* from frozen_req) and late_req.closureDate is not null"+" "+		
					           "union"+" "+
					            "select late_req.r_id as r_id,late_req.stageID as stage_id,current_date()-late_req.new_date as late_dur,request.in_system as in_sys	from late_req,request where late_req.r_id=request.r_id and late_req.r_id not in(select* from frozen_req) and late_req.closureDate is null");
			
	// rs will contain for each information system the number of lates and the sum of all lates
			rs=stmt.executeQuery("select sum(late_dur) as sum,count(*)as count,in_sys from late_req_true group by in_sys");
			
		  while (rs.next()){
			  t_rep.getNumber_late_requests().add(rs.getInt("count"));
			  t_rep.getSum_late_requests().add(rs.getInt("sum"));
			  Tardiness_report_in_array_ENTITY t_in_arr=new Tardiness_report_in_array_ENTITY();  
			  t_in_arr.setNumber_of_lates(rs.getInt("count"));
			  t_in_arr.setSum_of_lates(rs.getInt("sum"));
			  t_in_arr.setSystem_information(rs.getString("in_sys"));
			  t_rep.getArray_to_display().add(t_in_arr) ; 
			     
	          
		  }stmt.executeUpdate("drop view dates");
		  stmt.executeUpdate("drop view late_req");
			stmt.executeUpdate("drop view req_max_dates");
			stmt.executeUpdate("drop view frozen_req");
			stmt.executeUpdate("drop view late_req_true");
		  result=t_rep;
		  }catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} return result;
		} 

		public Object Acti(String[] s){
			long oneDayMilSec = 86400000; // number of milliseconds in one day
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Integer active_arr[]=null;
            Integer frozen_arr[]=null;
            Integer closed_arr[]=null;
            Integer rejected_arr[]=null;
			try {

			    java.util.Date startDate = sdf.parse(s[0]);
			    java.util.Date endDate = sdf.parse(s[1]);
			    
			    int array_size= (int)( (endDate.getTime() - startDate.getTime()) / (1000 * 60 * 60 * 24));

			    
			    active_arr=new Integer[array_size];
			     frozen_arr=new Integer[array_size];
			     closed_arr =new Integer[array_size];
			     rejected_arr=new Integer[array_size];
			    
			    
			    long startDateMilSec = startDate.getTime();
			    long endDateMilSec = endDate.getTime();
                 int x=-1;
			    for(long d=startDateMilSec; d<endDateMilSec; d=d+oneDayMilSec){
			    	 String to_query=new java.sql.Date(d).toString();
			    	 System.out.println(to_query);
			    	x=x+1; 
			      
			     try {
					rs=stmt.executeQuery("select distinct r_id from requeststatus");
					while (rs.next()){
						ArrayList<Report_per_day> rep= new ArrayList<Report_per_day>();
						rs1=stmt_mail.executeQuery("select reqStatus,DATE(reqDate) as date  from requeststatus where r_id='"+rs.getInt("r_id")+"' order by reqDate");	
						while(rs1.next()){
							Report_per_day day=new Report_per_day();
							day.setDate(rs1.getDate("date").toString());
							day.setStartDate(rs1.getDate("date"));
							day.setName(rs1.getString("reqStatus"));
							rep.add(day);
							
						}rs1.close();
						if(rep.size()==1){
						int s1=	new Date(d).compareTo(rep.get(0).getStartDate());
						if (s1>0||s1==0){
							if(rep.get(0).getName().equals("ACTIVE")){
								System.out.println(x);
								 if( active_arr[x]==null){
									 Integer in=new Integer(1);
									 active_arr[x]=in;
								 }
								 else{
									 Integer in=new Integer(active_arr[x].intValue()+1);
									 active_arr[x]=in;
								 }
								  
							  }
							  if (rep.get(0).getName().equals("FROZEN")){
									 if( frozen_arr[x]==null){
										 Integer in=new Integer(1);
										 frozen_arr[x]=in;
									 }
									 else{
										
										 System.out.println(frozen_arr[x].intValue()+1);
										 Integer in=new Integer(frozen_arr[x].intValue()+1);
										 frozen_arr[x]=in;
									 }
									  
								  }
							  if(rep.get(0).getName().equals("CLOSED")){
									 if( closed_arr[x]==null){
										 Integer in=new Integer(1);
										 closed_arr[x]=in;
									 }
									 else{
										 Integer in=new Integer(closed_arr[x].intValue()+1);
										 closed_arr[x]=in;
									 }
									  
								  }
							   if (rep.get(0).getName().equals("REJECTED")){
									 if( rejected_arr[x]==null){
										 Integer in=new Integer(1);
										 rejected_arr[x]=in;
									 }
									 else{
										 Integer in=new Integer(rejected_arr[x].intValue()+1);
										 rejected_arr[x]=in;
									 }
							
						}}
						}else{
						  for (int i = 1; i <rep.size(); i++) {
						
							 int is_now_befor= new Date(d).compareTo(rep.get(i-1).getStartDate());
							 int is_now_after=new Date(d).compareTo(rep.get(i).getStartDate());
								if((is_now_befor==0||is_now_befor>0)&&(is_now_after<0)){
							 if(rep.get(i-1).getName().equals("ACTIVE")){
									 if( active_arr[x]==null){
										 Integer in=new Integer(1);
										 active_arr[x]=in;
									 }
									 else{
										 Integer in=new Integer(active_arr[x].intValue()+1);
										 active_arr[x]=in;
									 }
									  
								  }
								  if (rep.get(i-1).getName().equals("FROZEN")){
										 if( frozen_arr[x]==null){
											 Integer in=new Integer(1);
											 frozen_arr[x]=in;
										 }
										 else{
											 Integer in=new Integer(frozen_arr[x].intValue()+1);
											 frozen_arr[x]=in;
										 }
										  
									  }
								  if(rep.get(i-1).getName().equals("CLOSED")){
										 if( closed_arr[x]==null){
											 Integer in=new Integer(1);
											 closed_arr[x]=in;
										 }
										 else{
											 Integer in=new Integer(closed_arr[x].intValue()+1);
											 closed_arr[x]=in;
										 }
										  
									  }
								   if (rep.get(i-1).getName().equals("REJECTED")){
										 if( rejected_arr[x]==null){
											 Integer in=new Integer(1);
											 rejected_arr[x]=in;
										 }
										 else{
											 Integer in=new Integer(rejected_arr[x].intValue()+1);
											 rejected_arr[x]=in;
										 }}}}
						if((new Date(d).compareTo(rep.get(rep.size()-1).getStartDate())>0)||(new Date(d).compareTo(rep.get(rep.size()-1).getStartDate()))==0){
							 if(rep.get(rep.size()-1).getName().equals("ACTIVE")){
								 if( active_arr[x]==null){
									 Integer in=new Integer(1);
									 active_arr[x]=in;
								 }
								 else{
									 Integer in=new Integer(active_arr[x].intValue()+1);
									 active_arr[x]=in;
								 }
								  
							  }
							  if (rep.get(rep.size()-1).getName().equals("FROZEN")){
									 if( frozen_arr[x]==null){
										 Integer in=new Integer(1);
										 frozen_arr[x]=in;
									 }
									 else{
										 
										
										 Integer in=new Integer(frozen_arr[x].intValue()+1);
										 frozen_arr[x]=in;
									 }
									  
								  }
							  if(rep.get(rep.size()-1).getName().equals("CLOSED")){
									 if( closed_arr[x]==null){
										 Integer in=new Integer(1);
										 closed_arr[x]=in;
									 }
									 else{
										 Integer in=new Integer(closed_arr[x].intValue()+1);
										 closed_arr[x]=in;
									 }
									  
								  }
							   if (rep.get(rep.size()-1).getName().equals("REJECTED")){
									 if( rejected_arr[x]==null){
										 Integer in=new Integer(1);
										 rejected_arr[x]=in;
									 }
									 else{
										 Integer in=new Integer(rejected_arr[x].intValue()+1);
										 rejected_arr[x]=in;
									 }}	
							
						}
						}} rs.close();}
					catch  (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

			     
			    }

			} catch (ParseException e) {
			    e.printStackTrace();
			}
			Activity_from_server send=new Activity_from_server() ;
		
					
			
			send.setActive(active_arr);
			send.setFrozen(frozen_arr);
			send.setClosed(closed_arr);
			send.setRejected(rejected_arr);
			return send;
			
		
		}

	
		
	
		
		

		/**
		 * This method get all the request per status and count the total duration
		 * @param acR is the entity the contain the started and finishing date for running the report
		 * @return
		 */
		public Object getActivityReportQuery(ActivityReport acR){
			ArrayList <Report_per_day> a=new ArrayList<Report_per_day>();
			try{
				stmt.executeUpdate("drop view help");	
				stmt.executeUpdate("CREATE VIEW help AS select r_id, reqStatus, DATE(reqDate)as date  from requeststatus");
				PreparedStatement preparedStatement = conn.prepareStatement("select reqStatus, date, COUNT(DISTINCT r_id) as countReq from help where date >= \""+acR.getFrom()+"\" and date<= \""+acR.getTo()+"\" group by reqStatus, date ORDER BY date ");
				rs = preparedStatement.executeQuery();
				while(rs.next()){
					if(rs.getString("reqStatus").equals("ACTIVE")){
						acR.setActiveReqList(rs.getInt("countReq"));
						
						
						acR.setClosedReqList(0);
						acR.setRejectReqList(0);
						acR.setFrozenReqList(0);
						System.out.println("1");
						//acR.setActiveReqDuration(rs.getInt("date"));
					}
					else if(rs.getString("reqStatus").equals("CLOSED")){
						acR.setActiveReqList(0);
						acR.setClosedReqList(rs.getInt("countReq"));
						
						acR.setRejectReqList(0);
						acR.setFrozenReqList(0);
						//acR.setActiveReqDuration(rs.getInt("date"));
					}
					else if(rs.getString("reqStatus").equals("REJECTED")){
						acR.setActiveReqList(0);
						acR.setClosedReqList(0);
						acR.setRejectReqList(rs.getInt("countReq"));
					
						acR.setFrozenReqList(0);
						//acR.setActiveReqDuration(rs.getInt("date"));
					}
					else if(rs.getString("reqStatus").equals("FROZEN")){
						acR.setActiveReqList(0);
						acR.setClosedReqList(0);
						acR.setRejectReqList(0);
						acR.setFrozenReqList(rs.getInt("countReq"));
						
						//acR.setActiveReqDuration(rs.getInt("date"));
					}	
				}
				preparedStatement = conn.prepareStatement("select reqStatus, requestStatus.r_id, sum(duration) as countDur from requestStatus, stageInReq where requestStatus.r_id = stageInReq.r_id  group by requestStatus.reqStatus, requestStatus.r_id ");
				rs = preparedStatement.executeQuery();
				while(rs.next()){
					if(rs.getString("reqStatus").equals("ACTIVE")){
						acR.setActiveReqDuration(rs.getInt("countDur"));
						System.out.println("2");
					}
					else if(rs.getString("reqStatus").equals("CLOSED")){
						acR.setClosedReqDuration(rs.getInt("countDur"));
					}
					else if(rs.getString("reqStatus").equals("REJECTED")){
						acR.setRejectReqDuration(rs.getInt("countDur"));
					}
					else if(rs.getString("reqStatus").equals("FROZEN")){
						acR.setFrozenReqDuration(rs.getInt("countDur"));
					}	
				}
						
			}catch(SQLException e) {e.printStackTrace();}
			return acR;		
		}
		
		
		/**
		 * this method will check the status of specific request
		 * @param req_id is the number of the request.
		 * @return the current status of the request:frozen/active/closed/unfrozen/rejected
		 */

		/**
	 * This method will add new request to the database
	 * @param req will contain request's details
	 * @return Integer that will comtain the request's number
	 */
	
	public Object addRequest(Request req){
		try {
			stmt.executeUpdate("INSERT INTO request (`in_system`,  `change_req`,`current_status`,`argument`,`comments`,`attached_files_path` ,`initiator`, `date_req`) " + " " +
					" VALUES ('"+req.getForm().getIn_system()+"', '"+req.getForm().getChange_request()+"','"+req.getForm().getCurrent_status()+"','"+req.getForm().getArgument()+"','"+req.getForm().getComments()+"', '"+req.getForm().getAttach_files_path()+"','"+req.getInitiator()+"' ,current_date())");
		
			
	       req.setRequest_id((Integer) this.getReq_number(req.getForm()));
	       //insert initiator into table userIn Request
	   	   stmt.executeUpdate("insert into userinreq (r_id,userName,autoNum) values ('"+req.getRequest_id()+"','"+req.getInitiator()+"','10')");
	   	   stmt.executeUpdate("insert into requeststatus (r_id ,reqStatus,reqDate) values ('"+req.getRequest_id()+"','WAITING',current_date())");
	   	   rs = stmt.executeQuery("select sys_incharge from information_system where sys_name ='"+req.getForm().getIn_system()+"'");
	   	   if(rs.next())
	   		stmt.executeUpdate("insert into userinreq (r_id,userName,autoNum) values ('"+req.getRequest_id()+"','"+rs.getInt("sys_incharge")+"','5')");
	   	   
	   	   result=req.getRequest_id();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return result;
		}
	/**
	 * this method will get all the requests needed to be close
	 * @return Array list with all the requests number
	 */
	
	public Object get_req_to_close(){
		ArrayList<Integer> req=new ArrayList<Integer>();
		try {
			
		
		stmt.executeUpdate("CREATE VIEW current_stage as select r_id,max(openDate) as open_date from stageinreq group by r_id");
		rs=stmt.executeQuery("select distinct stageinreq.r_id as r_id from stageinreq,current_stage where stageinreq.r_id=current_stage.r_id and current_stage.open_date=stageinreq.openDate and stageID=5 and closureDate is null");
	    while(rs.next()){
	    	Integer i =new Integer(rs.getInt("r_id"));
	    	req.add(i);
	    }
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
		}
		try {
			stmt.executeUpdate("drop view current_stage");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
       return req;   
	}
	/**
	 * this method will update the status of the request to "CLOSED"
	 * @param req is the number of the request
	 * @return Integer:1-the update sucseede,0- otherwise
	 */
	public Object close_req(int req){
		try {
			
			int bool=stmt.executeUpdate("INSERT INTO requeststatus (`r_id`,  `reqStatus`,`reqDate`) " + " " +
					" VALUES ('"+req+"','CLOSED',CURRENT_TIMESTAMP())");
			Integer int_bool=new Integer(bool);
			result=int_bool;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return result;
		
	}
	public Integer get_initiator(int req_num){
		Integer initiator;
		 try {
			rs = stmt.executeQuery("select initiator from request where r_id='"+req_num+"'");
		    if(rs.next()){
		    	initiator=new Integer(rs.getInt("initiator"));
		    	result=initiator;
		    }
		 } catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			result=null;
		}
		 return (Integer) result;
	}
	
	
	/**
	 * This method will get the request id of a request
	 * @param form will contain the request's form and by this parameters the method finds
	 * the request's id
	 * @return 
	 */
	
	public Object getReq_number(ReqForm form){
		
		
		try {
		
			 rs = stmt.executeQuery("SELECT r_id FROM request WHERE in_system='"+form.getIn_system()+"'AND current_status ='"+form.getCurrent_status()+"'AND  change_req='"+form.getChange_request()+"'AND  date_req=CURRENT_DATE()");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();}
			try {
				if (rs.next()){
					
				Integer	result_in=new Integer(rs.getInt("r_id"));
				result=result_in;}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
				return result;}
	/**
	 * this method gets all the frozen requests from the database
	 * @return Array list with all the frozen requests number
	 */
	public Object get_frozen_requests(){
		ArrayList<Integer>arr=new ArrayList<Integer>();
		try {
			
			stmt.executeUpdate("create view req_max_dates as select r_id,max(reqDate) as max_date from requeststatus group by r_id");
            rs=stmt.executeQuery("select requeststatus.r_id as r_id from requeststatus,req_max_dates where requeststatus.r_id=req_max_dates.r_id and requeststatus.reqStatus='FROZEN' and requeststatus.reqDate=req_max_dates.max_date"); 
            while(rs.next()){
            Integer i=new Integer(rs.getInt("r_id"));
            arr.add(i);
            }
		
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			stmt.executeUpdate("drop view req_max_dates");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return arr;
	}

	/**
	 * This method get the sum of durations of all the the approved extended request and the sum of all the returned stage's duration 
	 * @param par is the entity that contains the period of time to run the report on
	 * @return the entity with all data
	 */
	public Object getParformanceReportQuery(Parformance_report par){
		try {
			String s = "EXTENDED";
		
			stmt.executeUpdate("CREATE VIEW helps AS select status, numDay, DATE(date) as date  from extantionreq");
			PreparedStatement preparedStatement = conn.prepareStatement("select sum(numDay) as count from helps where status = 'EXTENDED' and date>=\""+par.getFrom()+"\""+" and date<=\""+par.getTo()+"\"" );
			rs = preparedStatement.executeQuery();
			if(rs.next()){
		
			par.setSumExtendedApp(rs.getInt("count"));}
			stmt.executeUpdate("drop view helps");
			
		} catch (SQLException e) {e.printStackTrace();}
		
		try {
			stmt.executeUpdate("create view temp as select r_id,stageID,openDate from stageinreq where (stageID='2' or stageID= '4') and  closureDate<> 'NULL'");
			stmt.executeUpdate("create view temp1 as (select r_id,stageID, MIN(openDate) as mini  from stageinreq where (stageID='2' or stageID= '4') and closureDate<> 'NULL' group by  r_id,stageID)");
			stmt.executeUpdate("create view temp2 as (select r_id,stageID,openDate from temp where openDate not in (select mini from temp1))");
			rs = stmt.executeQuery("select SUM(duration) as sum from stageinreq, temp2 where stageinreq.openDate=temp2.openDate group by temp2.r_id");
			if(rs.next())		
				par.setSumReturnStageDur(rs.getInt("sum"));
			    System.out.println(rs.getInt("sum"));
		
		
			stmt.executeUpdate("drop view temp");
		
		stmt.executeUpdate("drop view temp1");
		stmt.executeUpdate("drop view temp2");}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();}
			
		return par;

		
		
	}	
	
	
	
	/**
	 * This method create a new stage and initialize the stage's manager
	 * @param stageManager is an array witch contain requestId,stageID and stage's manager 
	 */
	public Object insertStageManagerQuery(int [] stageManager){
		PreparedStatement preparedStatement;
		try{	
			
			for(int i=1; i<stageManager.length; i++){
			//insert to table stageInRequest the stage's managers
				preparedStatement = conn.prepareStatement("insert into stageInReq (r_id,stageID,stageManagerId) values(?,?,?)");
				preparedStatement.setInt(1, stageManager[0]);
				preparedStatement.setInt(2, i);
				preparedStatement.setInt(3, stageManager[i]);
				preparedStatement.executeUpdate();
			}
			rs = stmt.executeQuery("select * from userInReq where r_id = '" +stageManager[0]+"'");
			while(rs.next()){
				for(int i=1;i<stageManager.length;i++){
					if(stageManager[i]==rs.getInt("userName"))
						stageManager[i] = 0;
				}
			}
			//insert the related managers to table userInRequest
			for(int i=1;i<stageManager.length;i++){
				if(stageManager[i]!=0){
					preparedStatement = conn.prepareStatement("insert into userInReq (r_id,userName,autoNum) values(?,?,?)");
					preparedStatement.setInt(1, stageManager[0]);
					preparedStatement.setInt(2, stageManager[i]);
					if(i!=4)
						preparedStatement.setInt(3,4+i);
					else
						preparedStatement.setInt(3,9);
					preparedStatement.executeUpdate();
				}
			}
	   		} 
		catch (SQLException e) {e.printStackTrace();}	
		return true;
	}
	/**
	 * this method get all the request in status waiting to approve 
	 * @return list with the waiting request
	 */
	public Object getWatingReq(){
		ArrayList<String> w_list = new ArrayList<String>();
		try{
			rs= stmt.executeQuery("select * from requeststatus where reqStatus=\"WAITING\"");
			while(rs.next()){
				w_list.add(rs.getString("r_id"));
			}
		}catch (SQLException e) {e.printStackTrace();}
		return  w_list;
	}
	/**
	 * this method will get all the information engineers from the database 
	 * @return array_list with the user names(id) of the information system engineers
	 */
     public ArrayList<String> get_info_eng(){
    	ArrayList<String> inf_eng=new ArrayList<String>();
    	 
    	 try {
			rs=stmt.executeQuery("select user_name from user where role=8");
		    while (rs.next()){
		    	inf_eng.add(rs.getString("user_name"));
		    }
    	 
    	 
    	 } catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
         return inf_eng;
     }
  /**
   * this method get's all the requests with status active   
   * @return list with all the active requests 
   */
     public ArrayList<Integer> getActiveRequest(){
    	 
    	 ArrayList<Integer> active = new ArrayList();
    	 try {
       		
			 stmt.executeUpdate("create view req_max_dates as select r_id,max(reqDate) as max_date from requeststatus group by r_id");
	    	 rs=stmt.executeQuery("select requeststatus.r_id as r_id from requeststatus,req_max_dates where requeststatus.r_id=req_max_dates.r_id and requeststatus.reqStatus='ACTIVE' and requeststatus.reqDate=req_max_dates.max_date");
	    	 
	    	 while(rs.next())
	    	 {
	    		 Integer i = new Integer(rs.getInt("r_id"));
	    		 active.add(i);
	    		 
	    	 }
	    	 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	try {
			stmt.executeUpdate("drop view req_max_dates");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return active; 
     }
     
 	/**
 	 * This method check if satge is allrdy opened and if it doe's, initialize the stage's duration
 	 * @param stageManager is an array witch contain requestId,stageID and stage's duration 
 	 */
 	public Object insertStageDurationQuery(int [] stageDur){
 		try{
 			for(int i=1;i<stageDur.length;i++){
 				if(stageDur[i]!=-1){
 					rs = stmt.executeQuery("SELECT * FROM stageinreq WHERE stageID='"+i+"' AND r_id='"+stageDur[0]+"'");
 					if(rs.next())
 						stmt.executeUpdate("UPDATE stageInReq SET duration ='"+stageDur[i]+"' WHERE r_id='"+stageDur[0]+"'AND stageID='"+i+"'");
 					else return (Object)false;
 					}
 				}
 			stmt.executeUpdate("update stageInReq set openDate=current_date() where r_id='"+stageDur[0]+"' and stageID=1");
 			stmt.executeUpdate("UPDATE requestStatus SET reqStatus =\"ACTIVE\",reqDate=current_date() where r_id="+stageDur[0]);
 		}
 		catch (SQLException e) {e.printStackTrace();}	
 		return (Object)true;
 	}
 	/**
 	 * This method get the current duration that the stage manager determine to get the approval of the supervisor
 	 * @param reqID is the request number
 	 * @return the duration of stages 1 and 3 (estimation and performance)
 	 */
 	public Object getCurrentDur(Integer reqID){
 		int [] temp = new int[2];
 		try {
			rs = stmt.executeQuery("SELECT duration FROM stageInReq WHERE stageID= 1 AND r_id=" + reqID);
			if(rs.next())
				temp[0] = rs.getInt("duration");
			rs = stmt.executeQuery("SELECT duration FROM stageInReq WHERE stageID=3 AND r_id=" + reqID);
			if(rs.next())
				temp[1] = rs.getInt("duration");
 		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
			return (Object)temp;	
 	}
 	/**
 	 * this method gets all details of a certain requst
 	 * @param reqID id the request ID
 	 * @return the form with all details
 	 */
 	public Object getReqDetails(Integer reqID){
 		ReqForm form = new ReqForm();
		try {
			rs = stmt.executeQuery("SELECT * FROM request WHERE  r_id=" + reqID);
			while(rs.next()){
				form.setIn_system(rs.getString("in_system"));
				form.setArgument(rs.getString("argument"));
				form.setChange_request(rs.getString("change_req"));
				form.setCurrent_status(rs.getString("current_status"));
				form.setComments(rs.getString("comments"));
				form.setAttach_files_path(rs.getString("attached_files_path"));
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return (Object) form;
 	}
 	/**
 	 * method that will get all the extension requests from the database
 	 * @return list with all the extension requests
 	 */
 	public Object getWaitingReqQuery() {

 		try {
 			ArrayList<Extention_Stage_req> waitList = new ArrayList<Extention_Stage_req>();
 			PreparedStatement preparedStatement = conn.prepareStatement("select r_id,stageName,arrgumant,numDay,date from extantionreq,stage where status = ? and extantionreq.stageID=stage.stageID ");
 			preparedStatement.setString(1, "WAITING");
 			rs = preparedStatement.executeQuery();
 			while (rs.next()) {
 				Extention_Stage_req esr = new Extention_Stage_req();
 				esr.setR_id(rs.getInt("r_id"));
 				esr.setStageName(rs.getString("stageName"));
 				esr.setArrgumant(rs.getString("arrgumant"));
 				esr.setNumDay(rs.getInt("numDay"));
 				esr.setTimestamp(rs.getTimestamp("date"));
 				// esr.setTimestamp(rs.getTimestamp("date"));
 				waitList.add(esr);
 			}
 			result = waitList;
 		} catch (SQLException e) {
 			e.printStackTrace();
 		}
 		return result;
 	} 
 	
 	/**
	 * This method insert the data of the estimation report entered by the estimator
	 * and close the  Estimation stage and open the examination stag
	 * @param the estimation report
	 * @return true is the saving completed and false otherwise
	 */
	public Object setEstimationReportQuery(EstimationReport esr){
		PreparedStatement preparedStatement;
		try{	
			preparedStatement = conn.prepareStatement("insert into estimationreport (r_id,changeRequired,benefit,risks,durationEstimation,constraints) values(?,?,?,?,?,?)");
			preparedStatement.setInt(1,esr.getR_id());
			preparedStatement.setString(2,esr.getChangeRequired());
			preparedStatement.setString(3,esr.getBenefit());
			preparedStatement.setString(4,esr.getRisks());
			preparedStatement.setInt(5,esr.getDurationEstimation());
			preparedStatement.setString(6,esr.getConstraints());
			preparedStatement.executeUpdate();
			
			rs = stmt.executeQuery("select in_system from request WHERE r_id="+esr.getR_id());
			if(rs.next()){
				stmt.executeUpdate("UPDATE estimationreport SET informationSystem ='"+rs.getString("in_system")+"' where r_id="+esr.getR_id());
				stmt.executeUpdate("UPDATE stageInreq SET closureDate =current_date() where stageID=1 and r_id="+esr.getR_id());
				stmt.executeUpdate("UPDATE stageInreq SET openDate=current_timestamp() where stageID=2 and r_id="+esr.getR_id());
			}
		} 
		catch (SQLException e) {e.printStackTrace(); return (Object)false;}	
		return (Object)true;
	}
	/**
	 * this method close the Performance stage and open the testing stag
	 * @param reqId is the request ID related to the stages
	 * @return
	 */
	public Object setPerformanceDecision(Integer reqId){
		try {
			stmt.executeUpdate("UPDATE stageInreq SET closureDate =current_date() where stageID=3 and r_id="+reqId);
			stmt.executeUpdate("UPDATE stageInreq SET openDate=current_timestamp() where stageID=4 and r_id="+reqId);

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return true;
		
	}

/**
 * this method get the estimation report for the given request
 * @param reqID is the request ID
 * @return esRep is the estimation report
 */
	public EstimationReport getEstimationReportQuery(int reqID){
		PreparedStatement preparedStatement;
		EstimationReport esRep = new EstimationReport();
		try {
			preparedStatement = conn.prepareStatement("select * from estimationreport where r_id = ?");
			preparedStatement.setInt(1,reqID );
			rs = preparedStatement.executeQuery();
			if(rs.next()){
				esRep.setBenefit(rs.getString("benefit"));
				esRep.setChangeRequired(rs.getString("changeRequired"));
				esRep.setDurationEstimation(rs.getInt("durationEstimation"));
				esRep.setRisks(rs.getString("risks"));
				esRep.setConstraints(rs.getString("constraints"));
			}
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
			return esRep;
	}
	/**
	 * this method get the system Information engineer who in charge of the system information in the given request  
	 * @param reqID is the request ID
	 * @return the system Information engineer ID
	 */
	public Object getSystemInchargQuery(int reqID){
		Integer sys_incharge = 0;
		try {
			PreparedStatement preparedStatement = conn.prepareStatement("select sys_incharge from information_system,request where r_id = ? and information_system.sys_name = request.in_system");
			preparedStatement.setInt(1,reqID);
			rs = preparedStatement.executeQuery();
			if(rs.next())
				sys_incharge =  (rs.getInt("sys_incharge"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return (Object)sys_incharge;
	}
	/**
	 * this method get the chairman of ICM system
	 * @return chairmanID
	 */
	public Object getChairManQuery(){
		Integer chairmanID=0;
		try {
			rs = stmt.executeQuery("SELECT user_name FROM user WHERE  role=3");
			if(rs.next())
				chairmanID =  (rs.getInt("user_name"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return (Object)chairmanID;
	}
	/**
	 * this method get the committee  of ICM system
	 * @return chairmanID
	 */
	public Object getCommittee(){
		ArrayList<Integer> committee = new ArrayList<Integer>();
		try {
			rs = stmt.executeQuery("SELECT user_name FROM user WHERE  role=3 || role=4");
			while(rs.next())
				committee.add(rs.getInt("user_name"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return (Object)committee;
	}
	/**
	 * this method get the examination's decision, if he approve the estimation report the request goto the next stage,
	 * if he  rejected the estimation report the request closed and if he ask for more details the request go bake to first stage  
	 * @param data contain the requestID and examination decision
	 * @return 
	 */
	public Object setExaminationStage(String[] data){
		try{
			if(data[1].equals("approved")){
				stmt.executeUpdate("UPDATE stageinreq SET closureDate =current_date() where stageID=2 and r_id="+data[0]);
				stmt.executeUpdate("UPDATE stageinreq SET openDate=current_timestamp() where stageID=3 and r_id="+data[0]);
			}
			else if(data[1].equals("rejected")){
				stmt.executeUpdate("UPDATE stageinreq SET closureDate =current_date() where stageID=2 and r_id="+data[0]);
				stmt.executeUpdate("UPDATE requeststatus SET reqStatus=\"REJECTED\" r_id="+data[0]);
			}else if(data[1].equals("more details")){
				stmt.executeUpdate("UPDATE stageinreq SET closureDate =current_date() where stageID=2 and r_id="+data[0]);
				rs =stmt.executeQuery("select * from stageinreq whrer stageID=1 and r_id ='"+data[0]+"'");
				if(rs.next())
					stmt.executeUpdate("insert into stageInreq (r_id,stageID,openDate,duration,stageManagerId) values ('"+data[0]+"',1,current_timestamp(),'"+rs.getInt("stageManagerId")+"')");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return true;
	}
	/**
	 * this method saved the failure report in the database
	 * @param fr is the failure report
	 * @return true if the saved successes
	 */
	public  Object  setFailureReport(FailureReport fr){
		PreparedStatement preparedStatement;
		try{	
			stmt.executeUpdate("insert into failurereport (r_id,date,decision,comments) values('"+fr.getReqID()+"',current_timestamp(),'"+fr.getDecision()+"','"+fr.getComments()+"')");
			rs =stmt.executeQuery("select * from stageinreq whrer stageID=3 and r_id ="+fr.getReqID());
			if(rs.next())
				stmt.executeUpdate("insert into stageInreq (r_id,stageID,openDate,duration,stageManagerId) values ('"+fr.getReqID()+"',1,current_timestamp(),'"+rs.getInt("stageManagerId")+"')");
		
			//preparedStatement.setInt(1,fr.getReqID());
			//preparedStatement.setString(2,fr.getDecision());
			//preparedStatement.setString(3,fr.getComments());
			stmt.executeUpdate("UPDATE stageInreq SET closureDate =current_date() where stageID=4 and r_id="+fr.getReqID());
			stmt.executeUpdate("UPDATE stageInreq SET openDate=current_timestamp() where stageID=3 and r_id="+fr.getReqID());

			}
		catch (SQLException e) {
			e.printStackTrace();}
		return true;
	}
	public Object setTstingStage(Integer req_id){
		try{
			stmt.executeUpdate("UPDATE stageInreq SET closureDate =current_date() where stageID=4 and r_id="+req_id);
			stmt.executeUpdate("UPDATE stageInreq SET openDate=current_timestamp() where stageID=5 and r_id="+req_id);
		}catch (SQLException e) {
			e.printStackTrace();}
		return true;
	}
	

	

	/**
	 * This method first checking if the stage is open and if the related request is ACTIVE and then checking for a specific stage if it was extended before or the date of extension request 
	    is lass than 3 days from the finishing date, if all conditions are apply the enter a new line into TABLE  "extantionReq" and WAITING for the supervisor approval
	 * @param stg is the entity that contain all details of the stage we like to extend   
	 * @return true if was'nt extended before and the date of extension request 
	    is lass than 3 days from the finishing date, else return false
	 */
	public Object CheckExtentionStageQuery(Extention_Stage_req stg){
		String s = new String();
		try{
			int stageId= getStageID(stg.getStageName());
			String ans1 =this.getRequestStatus(stg.getR_id()).toString();
			if(ans1.equals("ACTIVE")){
				s =  "SELECT status FROM extantionReq WHERE r_id = ? and stageId = ? ";
				PreparedStatement preparedStatement = conn.prepareStatement(s);
				preparedStatement.setInt(1,stg.getR_id());
				preparedStatement.setInt(2, stageId);
				rs = preparedStatement.executeQuery();
				if(rs.next()){
					if(rs.getString("status").equals("EXTENDED"))
						return 0;
					}
				else
					{
					stmt.executeUpdate("CREATE VIEW current_stage as select r_id,max(openDate) as open_date from stageinreq group by r_id");
					rs=stmt.executeQuery("select stageinreq.openDate,duration, current_timestamp() as currDate from stageinreq,current_stage where stageinreq.r_id=current_stage.r_id and current_stage.open_date=stageinreq.openDate and stageID='"+stageId+"' and closureDate is null and stageinreq.r_id='"+stg.getR_id()+"'");
						if(rs.next()){
							Calendar cal = Calendar.getInstance();
						    cal.setTime(rs.getDate("openDate"));
						    Calendar cal1 = Calendar.getInstance();
						    cal1.setTime(rs.getDate("currDate"));

							int oDay = cal.get(Calendar.DAY_OF_MONTH); 
							int cDay =cal1.get(Calendar.DAY_OF_MONTH); 
							if((oDay+ rs.getInt("duration")-cDay)<=3 )
								return 1;
							else 
							{
								s="insert into extantionReq (r_id,stageID,arrgumant,status,numDay,date) values (?,?,?,?,?,current_timestamp())";									preparedStatement = conn.prepareStatement(s);
								preparedStatement = conn.prepareStatement(s);
								preparedStatement.setInt(1, stg.getR_id());
								preparedStatement.setInt(2, stageId);
								preparedStatement.setString(3, stg.getArrgumant());
								preparedStatement.setString(4, "WAITING");
								preparedStatement.setInt(5, stg.getNumDay());
								preparedStatement.executeUpdate();
								return 3;
							}
						}	
						stmt.executeUpdate("drop view current_stage");
					}
				}
			}
			catch (SQLException e) {
				e.printStackTrace();}
			return 2;
		}
	
	
	/**
	 * this method get the stage ID from the DB
	 * @param s_name is the stage name
	 * @return the stage ID related to the stage name
	 */
	public int getStageID(String s_name){
		PreparedStatement preparedStatement;
		try {
			String selectSQL = "SELECT * FROM stage WHERE stageName = ?";
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setString(1, s_name);
			rs = preparedStatement.executeQuery();
			if(rs.next()){
				return  rs.getInt("stageID"); 
			}
		}catch (SQLException e) {}
		return 0;
	}
	/**
	 * this method will check the status of specific request
	 * @param req_id is the number of the request.
	 * @return the current status of the request:frozen/active/closed/unfrozen/rejected
	 */

	public Object getRequestStatus(int req_id){
		String status="";
			try {
				stmt.executeUpdate("CREATE VIEW dates AS select reqDate from requeststatus where r_id='"+req_id+"'");
				rs=stmt.executeQuery("select reqStatus from requeststatus where r_id='"+req_id+"' and reqDate=(select MAX(reqDate) from( dates ))");
				
				if (rs.next()){
					status=rs.getString("reqStatus");
				}	
				stmt.executeUpdate("drop view dates");
			} 	
			catch (SQLException e) {
				// TODO Auto-generated catch block
		
				e.printStackTrace();
			}
			return status;
	}

	/**
	 * This method check if the given stage exists and open
	 * @param: r_id is the request id and s_id is the stage id
	 * @return true if stage exists and open and false if not exists or closed
	 */
	public Object stageIsOpenQuery(int r_id, int s_id){
		try {

			stmt.executeUpdate("CREATE VIEW current_stage as select r_id,max(openDate) as open_date from stageinreq group by r_id");
			rs=stmt.executeQuery("select * from stageinreq,current_stage where stageinreq.r_id=current_stage.r_id and current_stage.open_date=stageinreq.openDate and stageID='"+s_id+"' and closureDate is null and r_id='"+r_id+"'");
			stmt.executeUpdate("drop view current_stage");
			if(rs.next())
				return true;
		} catch (SQLException e) {
			e.printStackTrace();}
		
		return false;
	}
	
	/**
	 * This method update the supervisor or the chairman by the chifDep 
	 * @param user us the entity we would like to update his role
	 * @return false if there is no supervisor or chairman already and true otherwise
	 */
	public Object updateSupervisorORchairmanQuery(User user){
		try{
			if(this.checkUserExsistsQuery(user)!=(Integer)0){	
				PreparedStatement preparedStatement = conn.prepareStatement("select * from user where user_name = ?");
				preparedStatement.setInt(1, user.getUserName());
				rs = preparedStatement.executeQuery();
				PreparedStatement preparedStatement1 = conn.prepareStatement("select * from user where role = ?");
				preparedStatement1.setInt(1, user.getRole());
				rs1 = preparedStatement1.executeQuery();
				if(rs.next()&&rs1.next()){
					if(rs.getInt("role")==1||rs.getInt("role")==2|| rs.getInt("role")==3)
						return 0;
					else{
						stmt.executeUpdate("UPDATE user SET role =8 where user_name="+ rs1.getInt("user_name"));
						stmt.executeUpdate("UPDATE user SET role =\""+user.getRole()+"\""+ "where user_name="+ user.getUserName());
						return 1;
						}
					}
				}else{


					PreparedStatement preparedStatement1 = conn.prepareStatement("select * from user where role = ?");
					preparedStatement1.setInt(1, user.getRole());
					rs1 = preparedStatement1.executeQuery();
					if(rs1.next()){
						stmt.executeUpdate("UPDATE user SET role =8 where user_name="+ rs1.getInt("user_name"));				
						stmt.executeUpdate("insert into user (user_name,password,role) values('"+user.getUserName()+"','1111','"+user.getRole()+"')");
						}
					}
			}catch (SQLException e) {e.printStackTrace();}
		
		return 2;
	}
	
	
	/**
	 * this method check if the user exist in the DB. only if the user
	 * does not exist we insert a new row with his details
	 * @param user contains the user's details
	 * @return Boolean value-true if the the user was updated in the db, false otherwise
	 */
	public Object updateInfoEngneer(User user){
		try{	
			if(this.checkUserExsistsQuery(user)==(Integer)0){
			stmt.executeUpdate("insert into user (user_name,password,role) values('"+user.getUserName()+"','1111','"+user.getRole()+"')");
			return true;
			}
		}catch (SQLException e) {e.printStackTrace();}		
		return false;
	}
	/**
	 * this method check if user exists in DB 
	 * @param user details
	 * @return the name (private & last name) of the user
	 */
	public Object checkUserExsistsQuery(User user){
		PreparedStatement preparedStatement;
		Integer userName=0;
		try {
			preparedStatement = conn.prepareStatement("select user_name from user where user_name = ?");
			preparedStatement.setInt(1, user.getUserName());
			rs = preparedStatement.executeQuery();
			//userName = rs.getString("name");
			if(rs.next())
				userName=rs.getInt("user_name");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return (Object)userName;
	}
	
	/**
	 * This method update the committee member by the chifDep and before check if exist in DB tow  committee member to able update
	 * @param user us the entity we would like to update his role
	 * @return false if there is committee member already exist or if there are no 2 members and true otherwise
	 */
	public Object updateCommitteeMember(User user){
		try{
			if(this.checkUserExsistsQuery(user)!=(Integer)0){
			PreparedStatement preparedStatement = conn.prepareStatement("select role, COUNT(user_name) as count from user where role = ? group by role");
			preparedStatement.setInt(1, user.getRole());
			rs = preparedStatement.executeQuery();
			PreparedStatement preparedStatement1 = conn.prepareStatement("select * from user where user_name= ?");
			preparedStatement1.setInt(1, user.getUserName());
			rs1 = preparedStatement1.executeQuery();
			if(rs1.next()&&rs.next()){
				if((rs1.getInt("role")==2 || rs1.getInt("role")==3 || rs1.getInt("role")==1))
					return 0;
				else if (!(rs.getInt("count")<2))
					return 1;
				else{
					stmt.executeUpdate("UPDATE user SET role =\""+user.getRole()+"\""+ "where user_name="+ user.getUserName());
					return 2;
				}
			}
			}else{
					PreparedStatement preparedStatement = conn.prepareStatement("select role, COUNT(user_name) as count from user where role = ? group by role");
					preparedStatement.setInt(1, user.getRole());
					rs = preparedStatement.executeQuery();
					if(rs.next()){
						if(rs.getInt("count")<2){
						stmt.executeUpdate("insert into user (user_name,password, role) values('"+user.getUserName()+"',1111,'"+user.getRole()+"')");
					}	
			}
			}
		}catch (SQLException e) {e.printStackTrace();}
		return 3 ;
	}
	
	
	/**
 	 * This method get in the DB the time duration that the first and third stage manager entered 
 	 * @param string array hold information about request number, stage name and duration
 	 */
	public Object setStageManagerDuration(String[] data){
		try{
			rs = stmt.executeQuery("SELECT * FROM stageInReq WHERE stageID='"+data[1]+"' AND r_id='"+data[0]+"'");
			if(rs.next()){
				stmt.executeUpdate("UPDATE stageInReq SET duration ='"+data[2]+"' WHERE r_id='"+data[0]+"'AND stageID='"+data[1]+"'");
				return true;
			}
		}catch (SQLException e) {e.printStackTrace();}	
 		return (Object)false;
	}
	
}
